﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Services;
using System.Web.Services;

public partial class LabPermintaanBarang : System.Web.UI.Page
{
    private static string strConn = ConfigurationManager.ConnectionStrings["LabConnectionString"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        populateBarang();
        populateLab();
        populateTahunAnggaran();
    }

    [WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = false)]
    public static DatabaseResponse GetKeteranganPermintaan(int lab, int barang, int tahun)
    {
        //sp_total_permintaan_barang_user 2, 16, 2017
        DatabaseResponse result = new DatabaseResponse();
        try
        {
            string query = "sp_total_permintaan_barang_user {0}, {1}, {2}";
            DataTable dt = new DataTable();
            using (SqlDataAdapter da = new SqlDataAdapter(string.Format(query, lab, barang, tahun), strConn))
            {
                da.Fill(dt);
            }

            if (dt.Rows.Count > 0)
            {
                result.Status = true;
                if (dt.Rows[0]["keterangan"] == DBNull.Value)
                {
                    result.Value = (string.Empty);
                }
                else
                {
                    result.Value = (dt.Rows[0]["keterangan"].ToString());
                }
            }
            else
            {
                result.Status = false;
                result.ErrorMessage = "Data total permintaan tidak ditemukan";
            }
        }
        catch (Exception ex)
        {
            result.Status = false;
            result.ErrorMessage = ex.Message;
        }
        return result;
    }

    [WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json, UseHttpGet = false)]
    public static DatabaseResponse Permintaan(int tahun, int barang, double jumlah, int lab, string keterangan, int user)
    {
        DatabaseResponse result = new DatabaseResponse();
        try
        {
            using (SqlConnection cn = new SqlConnection(strConn))
            {
                cn.Open();
                string query = "sp_barang_permintaan {0}, {1}, {2}, {3}, '{4}', {5}";
                using (SqlCommand cmd = new SqlCommand(string.Format(query, barang, tahun, jumlah, lab,  keterangan, user)))
                {
                    cmd.Connection = cn;
                    int ra = cmd.ExecuteNonQuery();
                    if (ra > 0)
                    {
                        result.Status = true;
                    }
                    else
                    {
                        result.Status = false;
                        result.ErrorMessage = "Tidak ada data yang berhasil ditulis.";
                    }
                }
                cn.Close();
            }
        }
        catch (Exception ex)
        {

            result.Status = false;
            result.ErrorMessage = ex.Message;
        }
        return result;
    }

    private void populateTahunAnggaran()
    {
        string query = "sp_get_tahun_anggaran";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        DataTable tahun = new DataTable();
        tahun.Columns.Add("TAHUN");
        if (dt.Rows.Count > 0)
        {
            int min = int.Parse(dt.Rows[0]["TAHUN1"].ToString());
            int max = int.Parse(dt.Rows[0]["TAHUN2"].ToString());
            for (int i = min; i <= max; i++)
            {
                DataRow dr = tahun.NewRow();
                dr["TAHUN"] = i;
                tahun.Rows.Add(dr);
            }
        }
        lstTahunAnggaran.DataSource = tahun;
        lstTahunAnggaran.DataBind();        
    }

    private void populateBarang()
    {
        string query = "sp_namabarang_list";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        lstBarang.DataSource = dt;
        lstBarang.DataBind();
    }

    private void populateLab()
    {
        string query = "sp_namalab_list";
        DataTable dt = new DataTable();
        using (SqlDataAdapter da = new SqlDataAdapter(query, strConn))
        {
            da.Fill(dt);
        }
        lstLab.DataSource = dt;
        lstLab.DataBind();
    }

    public class DatabaseResponse
    {
        public bool Status;
        public string ErrorMessage;
        public string Value;
    }
}